package hz;

import java.sql.*;
import java.util.ArrayList;

public class UserService {

	private static String url = "jdbc:mysql://localhost:3306/cs656";
	private static String username = "root";
	private static String password = "river0822";
	private static StringBuilder sb = new StringBuilder();

	static {
		sb.append(url).append('?').append("user=").append(username)
				.append("&password=").append(password);
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	static int createUser(String userid, String passwd) {

		if (dupUser(userid))
			return -1;

		// id, score, userid, passwd
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn.prepareStatement(
						"insert into users values (null, 0, ?, ?)",
						Statement.RETURN_GENERATED_KEYS);) {
			stat.setString(1, userid);
			stat.setString(2, passwd);
			stat.executeUpdate();
			ResultSet generatedKeys = stat.getGeneratedKeys();
			if (generatedKeys.next())
				return generatedKeys.getInt(1);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;

	}

	static void updateUserScore(long id, int score) {
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("update users set score = score + ? where id = ?");) {
			stat.setInt(1, score);
			stat.setLong(2, id);
			stat.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	static User[] globalRanking() {
		ResultSet rs = null;
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("select userid, score from users order by score desc limit 0, 10");) {

			rs = stat.executeQuery();

			ArrayList<User> users = new ArrayList<>();

			while (rs.next()) {
				User u = new User();
				u.setUserid(rs.getString("userid"));
				u.setScore(rs.getInt("score"));
				users.add(u);
			}

			return users.toArray(new User[] {});

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return new User[] {};
	}

	static boolean dupUser(String uid) {
		ResultSet rs = null;
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("select count(*) from users where userid = ?");) {
			stat.setString(1, uid);
			rs = stat.executeQuery();

			if (rs.next() && rs.getInt(1) == 0) {
				return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

		return true;
	}

	static int ranking(int id) {
		ResultSet rs = null;
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("select count(*) from users where score > (select score from users where id = ?)");) {
			stat.setInt(1, id);
			rs = stat.executeQuery();

			if (rs.next()) {
				return rs.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

		return -1;
	}

	static User popluateUser(int id) {

		User newUser = new User();
		ResultSet rs = null;
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("select * from users where id = ?");) {
			stat.setInt(1, id);
			rs = stat.executeQuery();

			if (rs.next()) {
				newUser.setId(rs.getLong("id"));
				newUser.setUserid(rs.getString("userid"));
				newUser.setScore(rs.getInt("score"));
				newUser.setRanking(-1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

		return newUser;
	}

	static User refreshUser(String userid, String passwd) {

		User newUser = new User();
		ResultSet rs = null;
		try (Connection conn = DriverManager.getConnection(sb.toString());
				PreparedStatement stat = conn
						.prepareStatement("select * from users where userid = ? and passwd = ?");) {
			stat.setString(1, userid);
			stat.setString(2, passwd);
			rs = stat.executeQuery();

			if (rs.next()) {
				newUser.setId(rs.getLong("id"));
				newUser.setUserid(rs.getString("userid"));
				newUser.setPasswd(rs.getString("passwd"));
				newUser.setScore(rs.getInt("score"));
				newUser.setRanking(-1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

		return newUser;
	}

}
